package com.janj.jdbc2;
//数据查询
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

public class DemoPhone {
    //定义CSR
    Connection conn = null ;
    Statement stat = null ;
    ResultSet resu = null ;

    @Before
    public void start(){
        try {
            String url = "jdbc:mysql://192.168.65.47:3306/duod9";
            String username ="root";
            String password ="root";
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

      //查询所有的手机价格,封装到集合,展示到控制台中
     @Test
    public void testFindAll(){
         try {
             //---------

             stat=conn.createStatement();
             String sql = "SELECT * FROM phone";
             resu = stat.executeQuery(sql);
             ArrayList<Phone> list = new ArrayList<>();
             while (resu.next()){
                 int idValue = resu.getInt("id");
                 String nameValue = resu.getString("name");
                 int priceValue = resu.getInt("price");
                 Phone phone = new Phone(idValue,nameValue,priceValue);
                 list.add(phone);
             }
               //展示结果数据
             for (Phone iphone : list) {
                 System.out.println(iphone);
             }
             //----------
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }


     //根据id,查询手机信息

      public Phone findPhoneById(int id){
          try {
              //定义SQL语句
           String sql ="SELECT * FROM phone WHERE id = "+id ;

              //获取到stat 的对象
              Statement stat = conn.createStatement();

              //查询数据
              ResultSet resu = stat.executeQuery(sql);
              if(resu.next()){
                  Phone phone = new Phone();
                  phone.setId(resu.getInt("id"));
                  phone.setName(resu.getString("name"));
                  phone.setPrice(resu.getInt("price"));
                  return phone ;
              }

          } catch (Exception e) {
              e.printStackTrace();
          }
              return null ;
      }

//定义测试学生的方法
      @Test
      public void testFindPhoneById(){
        Phone phone = findPhoneById(5);
          System.out.println("phone = " + phone);
      }

    @After
    public void end(){
      if(resu != null){
          try {
              resu.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }

        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
               conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
